package database;

import java.sql.*;
import java.util.Scanner;

public class TestPreparabledStatement {

    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);

        // 加载数据库驱动程序
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        String url = "jdbc:sqlserver://localhost:1433;databaseName=qw";
        String username = "sa";
        String password = "123456";

        Connection conn = null;
        PreparedStatement ps = null;

        System.out.println("请选择操作类型：\n1.查询用户信息\n2.添加新用户\n3.删除用户\n4.更新用户信息");
        int choice = input.nextInt();

        try {
            conn = DriverManager.getConnection(url, username, password);

            switch (choice) {
                case 1:
                    // 查询用户信息
                    System.out.println("请输入要查询的用户名：");
                    String queryUsername = input.next();
                    String selectSql = "SELECT * FROM users WHERE username LIKE ?";
                    ps = conn.prepareStatement(selectSql);
                    ps.setString(1, "%" + queryUsername + "%");
                    ResultSet resultSet = ps.executeQuery();
                    while (resultSet.next()) {
                        String un = resultSet.getString("username");
                        String em = resultSet.getString("email");
                        System.out.println("姓名：" + un + ", 邮箱：" + em);
                    }
                    break;
                case 2:
                    // 添加新用户
                    System.out.println("请输入新用户名：");
                    String newUsername = input.next();
                    System.out.println("请输入新用户邮箱：");
                    String newUserEmail = input.next();
                    String insertSql = "INSERT INTO users (username, email) VALUES (?, ?)";
                    ps = conn.prepareStatement(insertSql);
                    ps.setString(1, newUsername);
                    ps.setString(2, newUserEmail);
                    int rowsInserted = ps.executeUpdate();
                    if (rowsInserted > 0) {
                        System.out.println("新用户添加成功！");
                    } else {
                        System.out.println("新用户添加失败！");
                    }
                    break;
                case 3:
                    // 删除用户
                    System.out.println("请输入要删除的用户名：");
                    String deleteUsername = input.next();
                    String deleteSql = "DELETE FROM users WHERE username = ?";
                    ps = conn.prepareStatement(deleteSql);
                    ps.setString(1, deleteUsername);
                    int rowsDeleted = ps.executeUpdate();
                    if (rowsDeleted > 0) {
                        System.out.println("用户删除成功！");
                    } else {
                        System.out.println("未找到要删除的用户！");
                    }
                    break;
                case 4:
                    // 更新用户信息
                    System.out.println("请输入要更新的用户名：");
                    String updateUsername = input.next();
                    System.out.println("请输入新的邮箱地址：");
                    String newEmail = input.next();
                    String updateSql = "UPDATE users SET email = ? WHERE username = ?";
                    ps = conn.prepareStatement(updateSql);
                    ps.setString(1, newEmail);
                    ps.setString(2, updateUsername);
                    int rowsUpdated = ps.executeUpdate();
                    if (rowsUpdated > 0) {
                        System.out.println("用户信息更新成功！");
                    } else {
                        System.out.println("未找到要更新的用户！");
                    }
                    break;
                default:
                    System.out.println("无效的操作类型！");
                    break;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            try {
                if (ps != null) ps.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
